package com.ifnk.portaljava.dapWeb.advance;

import com.ifnk.portaljava.dapWeb.pojo.CDRrecord;
import com.ifnk.portaljava.dapWeb.pojo.MenuRecord;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class Subscription {
	Log log = LogFactory.getLog(Subscription.class);

	public static ArrayList readCDRrecord() {

		ArrayList arr = new ArrayList();
		DBAccess dbQuery = new DBAccess();
		ResultSet rs = null;
		// String sqlStr =
		// "select id,paramter_type,paramter_type_CH,paramter_name,paramter_name_CH,paramter_attribute,paramter_check,paramter_detail,paramter_html from cdrfield order by paramter_type,id;";
		String sqlStr = "select a.id,b.type p_type,a.p_type_CH,a.p_filteroption,a.p_displayname,a.p_name,a.p_name_CH,a.p_attribute,a.p_check,a.p_detail,a.p_protocol from cdrfield a,t_type_prm b, t_protocol c "
				+ "  where (a.p_displayname is not null) and a.p_type=b.type and a.p_protocol = c.protocol order by c.id,b.id,a.id;";
		rs = dbQuery.queryTable(sqlStr);
		try {

			while (rs.next()) {

				CDRrecord cdr = new CDRrecord();
				try {

					cdr.setId(rs.getInt("id"));
				} catch (Exception e) {
					cdr.setId(0);
					e.printStackTrace();
				}

				
				try {
					cdr.setType(rs.getString("p_type"));
				} catch (RuntimeException e) {
					cdr.setType("&nbsp;");
					e.printStackTrace();
				}
				

				try {
					cdr.setProtocol(rs.getString("p_protocol"));
				} catch (RuntimeException e) {
					cdr.setProtocol("&nbsp;");
					e.printStackTrace();
				}

				arr.add(cdr);

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			return arr;
		}

	}

	public static ArrayList readMenuRecord(String role_id) {

		ArrayList arr = new ArrayList();
		DBAccess dbQuery = new DBAccess();
		ResultSet rs = null;
		// String sqlStr =
		// "select id,paramter_type,paramter_type_CH,paramter_name,paramter_name_CH,paramter_attribute,paramter_check,paramter_detail,paramter_html from cdrfield order by paramter_type,id;";
		// String sqlStr =
		// "select a.* from (select id,menu_name,(case when parent = 0 then id else parent end) as parent, no,module_id from menu ) a "
		// +
		// "RIGHT OUTER JOIN (select * from menu where id in (select  distinct parent from menu) ) b on a.parent = b.id order by parent ,no;";
		//			
		String sqlStr = "select a.*,b.location from menu a LEFT OUTER JOIN "
				+ "( select * from module where id in (select distinct module_id from role_permission where role_id in ("
				+ role_id + "0)) and disabletime is null ) b "
				+ "on a.module_id = b.id order by a.parent,a.no,a.id";
		 System.out.println(sqlStr);

		rs = dbQuery.queryTable(sqlStr);
		try {

			while (rs.next()) {

				MenuRecord menu = new MenuRecord();
				try {
					menu.setId(rs.getInt("id"));
				} catch (Exception e) {
					menu.setId(0);
					e.printStackTrace();
				}

				try {
					menu.setMenu_name(rs.getString("menu_name"));
				} catch (RuntimeException e) {
					menu.setMenu_name("&nbsp;");
					e.printStackTrace();
				}

				try {
					menu.setParent(rs.getInt("parent"));
				} catch (RuntimeException e) {
					menu.setParent(0);
					e.printStackTrace();
				}

				try {
					menu.setNo(rs.getString("no"));
				} catch (RuntimeException e) {
					menu.setNo("&nbsp;");
					e.printStackTrace();
				}
				try {
					menu.setModule_id(rs.getInt("module_id"));
				} catch (RuntimeException e) {
					menu.setModule_id(0);
					e.printStackTrace();
				}

				try {
					menu.setLocation(rs.getString("location"));
					// System.out.println(menu.getModule_id());
				} catch (RuntimeException e) {
					menu.setLocation("&nbsp;");
					e.printStackTrace();
				}
				if (!((menu.getModule_id() != 0) && (menu.getLocation() == null))) {
					// System.out.println(menu.getId());
					arr.add(menu);
				}

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			arr = delBlankMenu(arr);
			return arr;
		}

	}
	private static ArrayList delBlankMenu(ArrayList arr) {
		// TODO Auto-generated method stub
		int i = 0;
		while(i<arr.size()){
			MenuRecord menu = (MenuRecord)arr.get(i);
			int id = menu.getId();
			int k = i;
			if(menu.getModule_id()==0){
			while(k<arr.size()){
				if(id == ((MenuRecord)arr.get(k)).getParent() ){
					break;
				}
				k++;
			}
			if(k==arr.size()){
				arr.remove(i);
				i--;
			}
			}
			i++;
		}
		return arr;
	}


}
